package com.hymjweb.demo.hymj.flight_manage.service;

import com.hymjweb.demo.framework.cache.bean.AMyCacheTreeNode;
import com.hymjweb.demo.framework.cache.service.GlobalCacheService;
import com.hymjweb.demo.framework.result.MIniUIGridResultMap;
import com.hymjweb.demo.framework.sys.BaseService;
import com.hymjweb.demo.framework.sys.SysConstant;
import com.hymjweb.demo.framework.util.DataTypeUtil;
import com.hymjweb.demo.framework.util.DateUtil;
import com.hymjweb.demo.framework.util.util.PoiUtil;
import com.hymjweb.demo.hymj.basic_data_manage.xt_upload_file.bean.XtUploadFilePojo;
import com.hymjweb.demo.hymj.basic_data_manage.xt_upload_file.service.XtUploadFileService;
import com.hymjweb.demo.hymj.flight_manage.mapper.BFlightMapper;
import com.hymjweb.demo.hymj.flight_manage.bean.BFlightPojo;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;

@Service
public class BFlightService extends BaseService {

    @Autowired
    private BFlightMapper bFlightMapper;

    @Autowired
    private GlobalCacheService globalCacheService;

    @Autowired
    private XtUploadFileService xtUploadFileService;


    public Object getPageList(Map map) {
        this.pageListInit(map);
        List list = bFlightMapper.findAll(map);
        MIniUIGridResultMap resultMap = this.pageToGrid(list);
        return resultMap;
    }

    public List<BFlightPojo> findByFlightDate(String flightDate){
        if(DataTypeUtil.isEmptyStr(flightDate)){
            return null;
        }
        return bFlightMapper.findByFlightDate(flightDate);
    }

    public List<AMyCacheTreeNode> findFlightNo(String airportId, String cityId, String airlinesId,String flightDate){
        if(DataTypeUtil.isEmptyStr(airportId) || DataTypeUtil.isEmptyStr(cityId) || DataTypeUtil.isEmptyStr(airlinesId)){
            return null;
        }
        return bFlightMapper.findFlightNo(airportId, cityId, airlinesId,flightDate);
    }


    /**
     * 根据条件加载航信息，注意这是供开单位页做下列表显示时使用
     * @param param
     * @return
     */
    public List<Map<String,Object>> queryFlightNoListByDropCnd(Map param) {

        // 数据库中查询出的
        List<Map<String,Object>> dbMaps = bFlightMapper.queryFlightNoListByDropCnd(param);

        // 转换后的
        List<Map<String,Object>> maps = DataTypeUtil.lineMapToHumpMap2(dbMaps);

        return maps;
    }


    @Transactional(rollbackFor = {Exception.class})
    public int insert(BFlightPojo pojo) throws Exception{
        if(pojo == null){
            throwException("航班添加参数错误!");
        }
        //检查UK
        BFlightPojo dbBFlightPojo = bFlightMapper.findUK(pojo);
        if(dbBFlightPojo != null){
            throwException("航班存在重复!");
        }

        pojo.setId(this.getSysStrId());
        pojo.setCreateTime(this.getSysTime());
        pojo.setCreateUserId(this.getLoginCzyId());
        pojo.setUpdateTime(this.getSysTime());
        pojo.setUpdateUserId(this.getLoginCzyId());

        return bFlightMapper.insert(pojo);
    }

    @Transactional(rollbackFor = {Exception.class})
    public int update(BFlightPojo pojo) throws Exception{
        if(pojo == null){
            throwException("航班更新参数错误!");
        }

        pojo.setUpdateTime(this.getSysTime());
        pojo.setUpdateUserId(this.getLoginCzyId());

        return bFlightMapper.update(pojo);
    }

    @Transactional(rollbackFor = {Exception.class})
    public int delete(String id) throws Exception{
        if(DataTypeUtil.isEmptyStr(id)){
            throwException("航班删除参数错误!!");
        }

        return bFlightMapper.delete(id);
    }


    @Transactional(rollbackFor = {Exception.class})
    public int batchTargetDateFlightByOriginalDate(Map map) throws Exception{
        if(map==null || map.isEmpty()){
            throwException("航班生成参数错误!");
        }

        String originalDate = DataTypeUtil.toStringObject(map.get("originalDate"));
        String targetDate = DataTypeUtil.toStringObject(map.get("targetDate"));
        // 原日期航班
        List<BFlightPojo> originalDateFlightList = findByFlightDate(originalDate);
        if(originalDateFlightList == null || originalDateFlightList.size() == SysConstant.INT_ZERO ){
            throwException("原日期航班不存在，根据原日期航班生成目标日期航班");
        }

        // 目标日期航班
        List<BFlightPojo> targetDateFlightList = findByFlightDate(targetDate);

        Date currentTime = getSysTime();
        String czyId = this.getLoginCzyId();

        // 合并,利用 Set集合的去重功能
        Set<BFlightPojo> newSet = new HashSet<>();
        for(BFlightPojo pojo:originalDateFlightList){
            pojo.setId(this.getSysStrId());
            pojo.setFlightDate(DateUtil.strToDate(targetDate));
            pojo.setUpdateTime(currentTime);
            pojo.setCreateUserId(czyId);
            pojo.setUpdateTime(currentTime);
            pojo.setUpdateUserId(czyId);
            newSet.add(pojo);
        }

        for(BFlightPojo pojo:targetDateFlightList){
            // 删除
            delete(pojo.getId());
            // 重新加入集合
            newSet.add(pojo);
        }

        List<BFlightPojo> pojos = new ArrayList<>(newSet);

        // 根据日期删除目标日期中的记录，再重新批量插入
        return bFlightMapper.batchInsert(pojos);
    }



    public int upload(MultipartFile upLoadFile) throws Exception{

        XtUploadFilePojo filePojo = xtUploadFileService.upload(upLoadFile);

        // 上传完成后进行数据导入工作
        int flag = excelToDatabase(filePojo);

        return flag;
    }

    /**
     * 把上传上来的excel文件读取后写入到数据表中
     *
     * @param filePojo
     * @return
     */
    @Transactional(rollbackFor = {Exception.class})
    public int excelToDatabase(XtUploadFilePojo filePojo) throws Exception{


        File destFile = xtUploadFileService.getFile(filePojo);

        // 定义读取文件的输入流
        FileInputStream fis = new FileInputStream(destFile);;

        Workbook wb = PoiUtil.getWorkBook(destFile, fis);

        Sheet sheet = wb.getSheetAt(0);

        int rows = sheet.getLastRowNum();

        if (rows < 2) {
            throw new Exception("Excel文件内容为空");
        }

        // 1.按行读取所有Excel中的每一行，形成一个List<Map>
        List<Map<String,Object>> listMap = new ArrayList<>();
        // 异常处理字符串
        StringBuffer exceptionInfoStr = new StringBuffer();

        for (int i = 1; i <= rows; i++){
            Map<String,Object> map = new HashMap<>();
            Row row = sheet.getRow(i);
            int rowNum = row.getRowNum() + 1;
            if (row != null){
                // 航空公司(1)airlines_id	 航班号(2)flight_no	 航班日期(3)flight_date
                // 航空公司
                String airlinesName = PoiUtil.getExcelCellValue(wb, row, 1);
                if(DataTypeUtil.isEmptyStr(airlinesName)){
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【航空公司】为空<br/>");
                }
                map.put("airlinesName",airlinesName);

                // 航班号
                String flightNo = PoiUtil.getExcelCellValue(wb, row, 2);
                if (DataTypeUtil.isEmptyStr(flightNo)) {
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【航班号】为空<br/>");
                }
                map.put("flightNo",flightNo);

                // 航班日期
                String flightDateStr = PoiUtil.getExcelCellValue(wb, row, 3);
                if (DataTypeUtil.isEmptyStr(flightDateStr)) {
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【航班日期】为空<br/>");
                }
                try {
                    Date flightDate = DateUtil.strToDate(flightDateStr);
                    map.put("flightDate",flightDate);
                }catch (Exception e){
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【航班日期】为非日期格式<br/>");
                }


                // 起飞时间(4)plan_take_off_time	 目的站(5)city_id	 始发起场(6)airport_id
                // 起飞时间
                String planTakeOffTime = PoiUtil.getExcelCellValue(wb, row, 4);
                if (DataTypeUtil.isEmptyStr(planTakeOffTime)) {
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【起飞时间】为空<br/>");
                }
                map.put("planTakeOffTime",planTakeOffTime);

                // 目的站
                String cityName = PoiUtil.getExcelCellValue(wb, row, 5);
                if (DataTypeUtil.isEmptyStr(cityName)) {
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【目的站】为空<br/>");
                }
                map.put("cityName",cityName);

                // 始发机场
                String airportName = PoiUtil.getExcelCellValue(wb, row, 6);
                if(DataTypeUtil.isEmptyStr(airportName)){
                    exceptionInfoStr.append("上传的excel文件中的【" + rowNum + "】行【始发机场】为空<br/>");
                }
                map.put("airportName",airportName);

                // 机型(7)model	 备注(8)comment
                String model = PoiUtil.getExcelCellValue(wb, row, 7);
                String comment = PoiUtil.getExcelCellValue(wb, row, 8);

                map.put("model",model);
                map.put("comment",comment);

                listMap.add(map);
            }
        }

        if(exceptionInfoStr.length()>0){
            // 删除上传的文件
            xtUploadFileService.deleteFile(filePojo);
            throw new Exception(exceptionInfoStr.toString());
        }
        // 用于存放翻译转后的 ID
        Map<String,String> flightCacheMap = new HashMap<>();

        List<BFlightPojo> bFlightPojoList = new ArrayList<>();

        Date currentTime = this.getSysTime();
        String czyId = this.getLoginCzyId();

        for(Map<String,Object> map : listMap){
            String airlinesName = map.get("airlinesName").toString();
            String flightNo = map.get("flightNo").toString();
            Date flightDate = (Date) map.get("flightDate");
            String planTakeOffTime = map.get("planTakeOffTime").toString();
            String cityName = map.get("cityName").toString();
            String airportName = map.get("airportName").toString();
            String model = map.get("model").toString();
            String comment = map.get("comment").toString();

            String airlinesId = flightCacheMap.get(airlinesName);
            if(airlinesId == null){
                airlinesId = globalCacheService.getIdByCacheNameAndName(SysConstant.CACHE_XT_AIRLINES,airlinesName);
                if(airlinesId != null){
                    flightCacheMap.put(airlinesName,airlinesId);
                }
                else{
                    exceptionInfoStr.append("上传的excel文件中的【航空公司:" + airlinesName + "】 在航空公司列表中找不到对应ID<br/>");
                }
            }

            String cityId = flightCacheMap.get(cityName);
            if(cityId == null){
                cityId = globalCacheService.getIdByCacheNameAndName(SysConstant.CACHE_XT_CITY,cityName);
                if(cityId != null){
                    flightCacheMap.put(cityName,cityId);
                }
                else{
                    exceptionInfoStr.append("上传的excel文件中的【目的站:" + cityName + "】 在系统城市列表中找不到对应ID<br/>");
                }
            }

            String airportId = flightCacheMap.get(airportName);
            if(airportId == null){
                airportId = globalCacheService.getIdByCacheNameAndName(SysConstant.CACHE_XT_AIRPORT,airportName);
                if(airportId != null){
                    flightCacheMap.put(airportName,airportId);
                }
                else{
                    exceptionInfoStr.append("上传的excel文件中的【始发机场:" + airportName + "】 在始发机场列表中找不到对应ID<br/>");
                }
            }

            /**
             * id
             * city_id, airport_id, airlines_id, flight_no, flight_date, plan_take_off_time, model, comment
             * create_time
             * create_user_id
             * update_time
             * update_user_id
             */
            // 生成对象
            BFlightPojo pojo = new BFlightPojo();
            // 暂时不设置 ID,因为这个对象不一定是要插入的，因为没有从数据库中去重
            pojo.setCityId(cityId);
            pojo.setAirportId(airportId);
            pojo.setAirlinesId(airlinesId);
            pojo.setFlightNo(flightNo);
            pojo.setFlightDate(flightDate);
            pojo.setPlanTakeOffTime(planTakeOffTime);
            pojo.setModel(model);
            pojo.setComment(comment);
            pojo.setCreateTime(currentTime);
            pojo.setCreateUserId(czyId);
            pojo.setUpdateTime(currentTime);
            pojo.setUpdateUserId(czyId);

            bFlightPojoList.add(pojo);
        }

        if(exceptionInfoStr.length() > 0){
            // 删除上传的文件
            xtUploadFileService.deleteFile(filePojo);
            throw new Exception(exceptionInfoStr.toString());
        }

        // 下面遍历对象集合，检测对象 UK 是否重复，如果重复则该对象不加入插入对象集合
        List<BFlightPojo> canInsertList = new ArrayList<>();
        for(BFlightPojo pojo:bFlightPojoList){
            BFlightPojo pojo1 = bFlightMapper.findUK(pojo);
            if(pojo1 == null){
                // 这个地方设置 ID,这个对象数据库中没有重复，所以可以入库了
                pojo.setId(this.getSysStrId());
                canInsertList.add(pojo);
            }
        }

        // 这就是要插入的POJO List
        int affectRows = bFlightMapper.batchInsert(canInsertList);

        return affectRows;
    }

}
